﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using WF_Business;

namespace SbBusiness.User
{
    public class SysUserTypeHandle
    {
        /// <summary>
        /// 由主键来删除一条用户类型-菜单关系记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DeleteMenuUserType(string id)
        {
            string strSql = string.Format(@"delete from sys_usertype where id='{0}'", id);

            int nReturn = SysParams.OAConnection().RunSql(strSql);
            return nReturn > 0 ? true : false;
        }


        /// 根据系统名称获取系统类型
        public string GetSysCodeBySysName(string name)
        {
            string strSql = "select SysCode from sys_usertype where sysname='" + name + "'";

            return SysParams.OAConnection().GetValue(strSql);
        }

        /// 根据系统名称获取用户类型
        public DataTable GetUserTypeNameBySysName(string name)
        {
            string strSql = @"select UserTypeCode from sys_usertype where sysname='" + name + "'";
            DataTable dtTemp = new DataTable();
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        ///插入新的用户类型
        public bool InsertUserType(Hashtable table)
        {
            string strSql = string.Format("select USERTYPECODE from SYS_USERTYPE where SYSCODE='{0}'",
                table["SYSCODE"]);

            string strResult = SysParams.OAConnection().GetValue(strSql);

            if (!string.IsNullOrEmpty(strResult))
            {
                strSql = string.Format(@"update SYS_USERTYPE set USERTYPENAME='{1}',
                        USERTYPECODE=(select max(usertypecode)+1 from SYS_USERTYPE ) where SYSCODE='{0}'",
                    table["SYSCODE"], table["USERTYPENAME"]);
            }
            else
            {
                strSql = string.Format(@"Insert into SYS_USERTYPE(SYSCODE,USERTYPENAME,USERTYPECODE,
                        SYSNAME) values('{0}','{1}',(select max(usertypecode)+1 from SYS_USERTYPE ),'{3}')", table["SYSCODE"],
                        table["USERTYPENAME"], table["SYSNAME"]);
            }

            return SysParams.OAConnection().RunSql(strSql) > 0 ? true : false;
        }

        /// <summary>
        /// 验证系统名
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public bool CheckSysName(string name)
        {
            string strSql = @"select UserTypeCode from sys_usertype where sysname='" + name + "'";
            string result = SysParams.OAConnection().GetValue(strSql);
           
            return (result != string.Empty);
        }

        /// <summary>
        /// 插入系统名
        /// </summary>
        /// <param name="strSysName"></param>
        /// <returns></returns>
        public bool InsertSysName(string strSysName)
        {
            string strSql = string.Format(@"Insert into SYS_USERTYPE(SYSCODE,SYSNAME) 
                values((select to_char(max(SYSCODE)+1,'000') from SYS_USERTYPE),'{0}')",
                strSysName);

            return SysParams.OAConnection().RunSql(strSql) > 0 ? true : false;
        }

        /// <summary>
        /// 获取系统类型
        /// </summary>
        /// <returns></returns>
        public DataTable GetSysCode()
        {
            string strSql = @"select distinct SYSCODE from sys_usertype";
            DataTable dtTemp = new DataTable();
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 获取系统类型和系统名称
        /// </summary>
        /// <returns></returns>
        public DataTable GetSysType()
        {
            string strSql = "select distinct syscode,sysname from sys_usertype ";
            DataTable dtTemp = new DataTable();
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// 获取系统名称
        public DataTable GetSysName()
        {
            string strSql = @"select distinct SYSNAME as 系统名称 from sys_usertype";
            DataTable dtTemp = new DataTable();
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 根据系统编码获取用户注册类型列表
        /// </summary>
        /// <param name="sysCode">系统编码</param>
        /// <returns></returns>
        public static DataTable GetRegisterType(string sysCode)
        {
            string strSql = string.Format(@"select UserTypeName,UserTypeCode from sys_UserType 
                where 1=1", sysCode);
            if (!string.IsNullOrEmpty(sysCode))
            {
                strSql += string.Format("  and SysCode='{0}'", sysCode);
            }

            DataTable dtTemp = new DataTable();
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 获取用户类型和类型名称
        /// </summary>
        /// <param name="syscode"></param>
        /// <returns></returns>
        public DataTable GetUsersType(string syscode)
        {
            string strSql = string.Format(@"select usertypecode,usertypename from sys_usertype where syscode='{0}'", syscode);
            DataTable dtTemp = new DataTable();
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 获取所有用户的用户类型和类型名称
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllUserType()
        {
            string strSql = "select usertypecode,usertypename from sys_usertype";
            DataTable dtTemp = new DataTable();
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 获取满足条件的系统帐号，单位名列表
        /// </summary>
        /// <param name="strSysTypeCode"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        public DataTable GetUserIDAndUserName(string strSysTypeCode, string strUnitName)
        {
            string strSql = string.Format(@"select username as 单位名,userid as 用户帐号 
                from sys_user a join sys_usertype b on a.type=b.usertypecode 
                where syscode='{0}' and username like '%{1}%' and ISVALID=1", strSysTypeCode, strUnitName);
            DataTable dtTemp = new DataTable();
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 获取子系统的帐号，单位名列表
        /// </summary>
        /// <param name="strSysTypeCode"></param>
        /// <returns></returns>
        public DataTable GetUserIDAndUserName(string strSysTypeCode)
        {
            string strSql = string.Format(@"select username as 单位名,userid as 用户帐号 
                    from sys_user a join sys_usertype b
                    on a.type=b.usertypecode where syscode='{0}' and a.ISVALID=1", strSysTypeCode);
            DataTable dtTemp = new DataTable();
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 根据登陆帐号获取用户类型名
        /// </summary>
        /// <param name="userID">登陆帐号</param>
        /// <returns></returns>
        public static string GetTypeNameByUserID(string userID)
        {
            string strSql = string.Format(@"select UserTypeName from sys_usertype where UserTypeCode=(select 
                type from sys_user where UserID='{0}')", userID);
            try
            {
                return SysParams.OAConnection().GetValue(strSql);
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// 根据用户类型名获取用户类型代码
        /// </summary>
        /// <param name="name">用户类型名</param>
        /// <returns></returns>
        public string GetUserTypeByName(string name)
        {
            string strSql = "select USERTYPECODE from sys_usertype where USERTYPENAME='" + name + "'";

            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 获取用户类型表
        /// </summary>
        /// <returns></returns>
        public DataTable GetUserTypes()
        {
            string strSql = @"select ID,SYSCODE as 系统代号,USERTYPENAME as 用户类型名,USERTYPECODE as 用户类型代码,
                               SYSNAME as 系统名称 from sys_usertype order by syscode";
            DataTable dtTemp;
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

    }
}
